# 查询 PostgreSQL 记录  --->  增删改查
import psycopg2


def get_connect():
    conn = psycopg2.connect(database='zhizhi', user='docker', password='000000',
                            host='localhost', port='5432')
    return conn


def save_record(save_sql):
    # 获取连接
    conn = get_connect()

    # 创建指针对象
    cur = conn.cursor()

    # 创建表
    # cur.execute("CREATE TABLE student(id integer,name varchar,sex varchar);")

    # 插入数据
    # cur.execute("INSERT INTO student(id,name,sex)VALUES(%s,%s,%s)", (1, 'Aspirin', 'M'))
    cur.execute(save_sql)

    # 提交数据 提交任何未提交的事务（transaction）到数据库 & 关闭连接
    conn.commit()
    cur.close()
    conn.close()


def get_count(count_sql):
    # 获取连接
    conn = get_connect()

    # 创建指针对象
    cur = conn.cursor()

    # 执行查询SQL
    cur.execute(count_sql)

    # 返回查询一条记录
    result = cur.fetchone()

    # 关闭连接
    cur.close()
    conn.close()

    return result[0]


def query_record_list(query_sql):
    # 获取连接
    conn = get_connect()

    # 创建指针对象
    cur = conn.cursor()

    # 执行查询SQL
    cur.execute(query_sql)

    # 返回查询记录
    rows = cur.fetchall()

    # 关闭连接
    cur.close()
    conn.close()

    return rows


def query_record_one(query_sql):
    # 获取连接
    conn = get_connect()

    # 创建指针对象
    cur = conn.cursor()

    # 执行查询SQL
    cur.execute(query_sql)

    # 返回查询记录
    result = cur.fetchone()

    # 关闭连接
    cur.close()
    conn.close()

    return result


# query_sql = "select t.name,t.type,t.address,t.poi_id from shenyang_poi t limit 10"
# rows = query_record_list(query_sql)
# print('----->1' + str(rows))
#
# rows = query_record_one(query_sql)
# print('----->2' + str(rows))
#
# count_sql = "select count(1) from shenyang_poi t "
# count = get_count(count_sql)
# print('------->count:' + str(count))
